package persistency;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.container.Eigenschappen;

public class DatabaseToegang {
	static final String DATABASE_URL = Eigenschappen.eigenschappenLijst.getProperty("url");
	static final String DATABASE_USER = Eigenschappen.eigenschappenLijst.getProperty("user");
	static final String DATABASE_PWD = Eigenschappen.eigenschappenLijst.getProperty("passwdVero");
	String databaseCommand;
	Connection connection = null;
	Statement statement = null;
	ResultSet resultSet = null;
	Boolean setResult = false; 
	Boolean connectieMetDB;

	public ArrayList<String> getFromDB (String dbCommand)
	{
		ArrayList<String> lijst = new ArrayList<String>();
		try
		{
			String resultaat;
			connection = DriverManager.getConnection(DATABASE_URL, DATABASE_USER, DATABASE_PWD);
			statement = connection.createStatement();
			resultSet = statement.executeQuery(dbCommand);
			ResultSetMetaData metaData = resultSet.getMetaData();
			int numberOfColumns = metaData.getColumnCount();
			while(resultSet.next())
			{
				resultaat = "";
				for (int i = 1; i <= numberOfColumns; i++)
				{
					resultaat += resultSet.getObject(i).toString();
					if (i<numberOfColumns)
						resultaat += ";";
				}
				lijst.add(resultaat);
			}
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			try
			{
				if (resultSet != null)
					resultSet.close();
				if (statement != null)
					statement.close();
				if (connection != null)
					connection.close();
			}
			catch (Exception ex)
			{
				ex.printStackTrace();
			}
		}
		return lijst;
	}
	
	public void setInDB (String dbCommand)
	{
		ArrayList<String> lijst = new ArrayList<String>();
		try
		{
			String resultaat;
			connection = DriverManager.getConnection(DATABASE_URL, DATABASE_USER, DATABASE_PWD);
			statement = connection.createStatement();
			setResult = statement.execute(dbCommand);
		}
		catch (SQLException ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			try
			{
				if (statement != null)
					statement.close();
				if (connection != null)
					connection.close();
			}
			catch (Exception ex)
			{
				ex.printStackTrace();
			}
		}
	}
	
	public void controleerConnectie(Connection connection)
	{
		if(connection != null)
		{
		connectieMetDB = true;
		}
		else
		{
			throw new IllegalStateException("Not connected to BD");
		}
	}
	
	public void disconnectFromDB()
	{
		if(connectieMetDB==true)
		{
			try
			{
				resultSet.close();
				statement.close();
				connection.close();
			}catch(SQLException sqlException)
			{
				sqlException.printStackTrace();
			}
			finally
			{
				connectieMetDB = false;
			}
		}
	}	
}
